﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.Web;

    public class AttendLeaveDao
    {
        public bool Back(SqlConnection conn, int leaveId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Leave] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = 3, ");
            builder.Append("\t[DestroyTime] = GETDATE() ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] IN (1) ");
            builder.Append("\tAND [Status] = 1 ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LeaveId", SqlDbType.Int);
            commandParameters[0].Value = leaveId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Confim(SqlConnection conn, int leaveId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Leave] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@LeaveId", SqlDbType.Int) };
            commandParameters[0].Value = leaveId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Delete(SqlConnection conn, int leaveId, string userId, bool isAdministrator)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Leave] ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId ");
            builder.Append("\tAND ");
            builder.Append("\t( ");
            builder.Append("\t\t([UserId] = @UserId AND [Allow] IN(0,2)) ");
            builder.Append("\t\tOR @IsAdministrator = 1 ");
            builder.Append("\t) ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@LeaveId", SqlDbType.Int);
            commandParameters[0].Value = leaveId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool DeleteByDateTime(SqlConnection conn, DateTime beingDate, DateTime endDate)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Leave] ");
            builder.Append("WHERE ");
            builder.Append("\t( ");
            builder.Append("\t\t[LeaveDate1] >= @BeginDate ");
            builder.Append("\t\tAND [LeaveDate1] <= @EndDate ");
            builder.Append("\t) ");
            builder.Append("\tOR ");
            builder.Append("\t( ");
            builder.Append("\t\t[LeaveDate2] >= @BeginDate ");
            builder.Append("\t\tAND [LeaveDate2] <= @EndDate ");
            builder.Append("\t) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            commandParameters[0].Value = beingDate;
            commandParameters[1] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            commandParameters[1].Value = endDate;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Destroy(SqlConnection conn, int leaveId, DateTime destroyTime)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Leave] ");
            builder.Append("SET ");
            builder.Append("\t[Status] = 2, ");
            builder.Append("\t[DestroyTime] = @DestroyTime ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId\t ");
            builder.Append("\tAND [Status] = 1 ");
            builder.Append("\tAND [Allow] IN (3) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LeaveId", SqlDbType.Int);
            commandParameters[0].Value = leaveId;
            commandParameters[1] = new SqlParameter("@DestroyTime", SqlDbType.DateTime);
            commandParameters[1].Value = destroyTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, AttendLeaveData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Attend_Leave] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[LeaderId], ");
            builder.Append("\t[LeaveType], ");
            builder.Append("\t[LeaveDate1], ");
            builder.Append("\t[LeaveDate2], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Allow], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[DestroyTime] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@LeaderId, ");
            builder.Append("\t@LeaveType, ");
            builder.Append("\t@LeaveDate1, ");
            builder.Append("\t@LeaveDate2, ");
            builder.Append("\t@Status, ");
            builder.Append("\t@Allow, ");
            builder.Append("\t@Reason, ");
            builder.Append("\t@DestroyTime ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[9];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@LeaderId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.LeaderId;
            commandParameters[2] = new SqlParameter("@LeaveType", SqlDbType.NText);
            if (data.LeaveTypeIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.LeaveType;
            }
            commandParameters[3] = new SqlParameter("@LeaveDate1", SqlDbType.DateTime);
            commandParameters[3].Value = data.LeaveDate1;
            commandParameters[4] = new SqlParameter("@LeaveDate2", SqlDbType.DateTime);
            commandParameters[4].Value = data.LeaveDate2;
            commandParameters[5] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[5].Value = data.Status;
            commandParameters[6] = new SqlParameter("@Allow", SqlDbType.Int);
            commandParameters[6].Value = data.Allow;
            commandParameters[7] = new SqlParameter("@Reason", SqlDbType.NText);
            if (data.ReasonIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Reason;
            }
            commandParameters[8] = new SqlParameter("@DestroyTime", SqlDbType.DateTime);
            if (data.DestroyTimeIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.DestroyTime;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool Reason(SqlConnection conn, int leaveId, string reason)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Leave] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = 2, ");
            builder.Append("\t[Reason] = @Reason ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LeaveId", SqlDbType.Int);
            commandParameters[0].Value = leaveId;
            commandParameters[1] = new SqlParameter("@Reason", SqlDbType.NText);
            commandParameters[1].Value = reason;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectAdmin(SqlConnection conn)
        {
            AttendParaDao dao = new AttendParaDao();
            dao.GetPara();
            string attendManager = dao.AttendManager;
            if (("," + attendManager + ",").IndexOf("," + HttpContext.Current.User.Identity.Name + ",") == -1)
            {
                return new DataTable();
            }
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Leave].*, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Leave], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Leave].[Allow] IN(0,3) ");
            builder.Append("\tAND [Attend_Leave].[Status] IN(1) ");
            builder.Append("\tAND [Attend_Leave].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Leave].[LeaveId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Attend_Leave";
            return set.Tables[0];
        }

        public DataTable SelectByLeaveId(SqlConnection conn, int leaveId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[LeaveId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[LeaderId], ");
            builder.Append("\t[LeaveType], ");
            builder.Append("\t[LeaveDate1], ");
            builder.Append("\t[LeaveDate2], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Allow], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[DestroyTime] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Leave] ");
            builder.Append("WHERE ");
            builder.Append("\t[LeaveId] = @LeaveId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@LeaveId", SqlDbType.Int) };
            commandParameters[0].Value = leaveId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Leave";
            return set.Tables[0];
        }

        public DataTable SelectDestroy(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Leave].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Leave], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Leave].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Leave].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND [Attend_Leave].[Allow] IN (1) ");
            builder.Append("\tAND [Attend_Leave].[Status] IN(1) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Leave].[LeaveId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Leave";
            return set.Tables[0];
        }

        public DataTable SelectHistory(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Leave].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Leave], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Leave].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Leave].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND [Attend_Leave].[Status] IN(2) ");
            builder.Append("\tAND Year([Attend_Leave].[LeaveDate1]) = Year(GETDATE()) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Leave].[LeaveId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Leave";
            return set.Tables[0];
        }

        public DataTable SelectMy(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Leave].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Leave], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Leave].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Leave].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND [Attend_Leave].[Status] = 1 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Leave].[LeaveId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Leave";
            return set.Tables[0];
        }
    }
}

